{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "20250129 20250205\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2025-01-29\n",
      "post end no value 2025-01-29\n",
      "2025-01-30\n",
      "post end no value 2025-01-30\n",
      "2025-01-31\n",
      "post end no value 2025-01-31\n",
      "2025-02-01\n",
      "post end no value 2025-02-01\n",
      "2025-02-02\n",
      "post end no value 2025-02-02\n",
      "2025-02-03\n",
      "post end no value 2025-02-03\n",
      "2025-02-04\n",
      "post end no value 2025-02-04\n",
      "2025-02-05\n",
      "post end 2025-02-05 1.6351\n",
      "             日期     10年\n",
      "0    2022-12-13   3.048\n",
      "1    2022-12-14   3.003\n",
      "2    2022-12-15  3.0331\n",
      "3    2022-12-16  3.0356\n",
      "4    2022-12-19  3.0206\n",
      "..          ...     ...\n",
      "525  2025-01-17    1.67\n",
      "526  2025-01-20   1.685\n",
      "527  2025-01-21  1.6676\n",
      "528  2025-01-22  1.6751\n",
      "0    2025-02-05  1.6351\n",
      "\n",
      "[530 rows x 2 columns]\n",
      "数据已成功保存至 ../data/2024_tenY_cdb.xlsx\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "from datetime import datetime, timedelta\n",
    "import asyncio\n",
    "\n",
    "# 请求的URL\n",
    "url = \"https://yield.chinabond.com.cn/gkh/yield\"\n",
    "# 爬虫存入文件地址\n",
    "file_path = '../data/2024_tenY_cdb.xlsx'\n",
    "\n",
    "# 转成dataframe 数据格式\n",
    "data = {\n",
    "  '日期': [],\n",
    "  '10年': []\n",
    "}\n",
    "old_data = pd.DataFrame(data)\n",
    "# 尝试读取 Excel 文件\n",
    "try:\n",
    "  # 如果文件存在，读取 Excel 文件\n",
    "  old_data = pd.read_excel(file_path)\n",
    "except FileNotFoundError:\n",
    "  print(f\"文件 {file_path} 不存在，使用默认数据创建 DataFrame\")\n",
    "\n",
    "\n",
    "def save_table():\n",
    "  new_data = pd.DataFrame(data)\n",
    "  # table_data = pd.merge(old_data, new_data, on=['日期', '10年'], how='outer')\n",
    "  table_data = pd.concat([old_data, new_data], axis=0)\n",
    "  # 对 '日期' 列进行升序排序\n",
    "  table_data = table_data.sort_values(by='日期', ascending=True)\n",
    "  table_data = table_data.drop_duplicates(subset='日期', keep='first')\n",
    "  print(table_data)\n",
    "\n",
    "  # 使用to_excel方法将DataFrame保存为Excel文件\n",
    "  try:\n",
    "    table_data.to_excel(file_path, index=False)  # 设置index=False表示不保存行索引\n",
    "    print(f\"数据已成功保存至 {file_path}\")\n",
    "  except Exception as e:\n",
    "    print(f\"保存数据时出现错误: {e}\")\n",
    "\n",
    "\n",
    "async def set_sign_data(date):\n",
    "  # 请求参数\n",
    "  params = {\n",
    "    \"searchDate\": date\n",
    "  }\n",
    "  try:\n",
    "    # 发送POST请求\n",
    "    # print(date)\n",
    "    # print('post start')\n",
    "    response = requests.post(url, data=params)\n",
    "    response.raise_for_status()  # 如果请求出现错误，抛出异常\n",
    "\n",
    "    # 使用BeautifulSoup解析HTML\n",
    "    soup = BeautifulSoup(response.text, 'html.parser')\n",
    "    # 根据选择器获取元素并提取文本内容\n",
    "    element = soup.select('#conter tr:nth-child(6) td:nth-child(2)')\n",
    "    if element:\n",
    "      value = element[0].get_text(strip=True)\n",
    "      data['10年'].append(value)\n",
    "      data['日期'].append(date)\n",
    "      print('post end', date, value)\n",
    "    else:\n",
    "      print('post end no value', date)\n",
    "  except requests.RequestException as e:\n",
    "    print(f\"请求出现错误: {e}\")\n",
    "\n",
    "\n",
    "async def set_data_by(start_date_str, end_date_str):\n",
    "  # 将输入的日期字符串转换为日期对象\n",
    "  start_date = datetime.strptime(start_date_str, '%Y%m%d')\n",
    "  end_date = datetime.strptime(end_date_str, '%Y%m%d')\n",
    "\n",
    "  # 判断开始日期是否大于等于结束日期，如果是则报错\n",
    "  if start_date > end_date:\n",
    "    raise ValueError(\"开始日期必须小于结束日期\")\n",
    "\n",
    "  # 循环打印日期\n",
    "  current_date = start_date\n",
    "  while current_date <= end_date:\n",
    "    current_date_str = current_date.strftime('%Y-%m-%d')\n",
    "    if current_date_str not in old_data['日期']:\n",
    "      print(current_date_str)\n",
    "      await asyncio.sleep(0.5)  # 避免过快发送请求\n",
    "      await set_sign_data(current_date_str)\n",
    "    current_date += timedelta(days=1)\n",
    "  save_table()\n",
    "\n",
    "# 获取当前日期\n",
    "\n",
    "# 计算6个月前的日期（可按需调整时间范围，这里以10天为例）\n",
    "\n",
    "\n",
    "def set_during10_before(days):\n",
    "  default_days = 7\n",
    "  end_date = datetime.now()\n",
    "  end_date = end_date - timedelta(days=days)\n",
    "  start_date = end_date - timedelta(days=default_days)\n",
    "  end_date_str = end_date.strftime('%Y%m%d')  # 格式 20240101\n",
    "  start_date_str = start_date.strftime('%Y%m%d')\n",
    "  return [start_date_str, end_date_str]\n",
    "\n",
    "\n",
    "def main():\n",
    "  start_date_str, end_date_str = set_during10_before(0)\n",
    "  print(start_date_str, end_date_str)\n",
    "\n",
    "  # 如果环境中已运行事件循环\n",
    "  try:\n",
    "    loop = asyncio.get_running_loop()\n",
    "  except RuntimeError:\n",
    "    loop = None\n",
    "\n",
    "  if loop and loop.is_running():\n",
    "    # 如果事件循环正在运行\n",
    "    task = set_data_by(start_date_str, end_date_str)\n",
    "    asyncio.ensure_future(task)  # 创建任务并调度运行\n",
    "  else:\n",
    "    # 如果没有事件循环，则使用 asyncio.run()\n",
    "    asyncio.run(set_data_by(start_date_str, end_date_str))\n",
    "\n",
    "\n",
    "main()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
